package com.tc.excel;

import com.tc.excel.anno.ExcelProperty;
import com.tc.excel.anno.ResponseExcel;
import com.tc.excel.enums.ExcelContentType;
import com.tc.excel.enums.FontColorEnum;
import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.util.ReflectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.Boolean;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Auther: tianchao
 * @Date: 2025/2/14 15:52
 * @Description:
 */
public class JxlExcelUtils {

    private static final Logger log = LoggerFactory.getLogger(JxlExcelUtils.class);

    private static ExcelFieldFilter excelFieldFilter = new ExcelFieldFilter(ExcelProperty.class);
    private static final String rowColSeparator = "$$$$";

    private static final Map<FontColorEnum, Colour> colorMap = new HashMap<>();

    static {
        colorMap.put(FontColorEnum.RED,Colour.RED);
        colorMap.put(FontColorEnum.BLUE,Colour.BLUE);
        colorMap.put(FontColorEnum.BLACK,Colour.BLACK);
        colorMap.put(FontColorEnum.GREEN,Colour.GREEN);
    }

    public static <T> void  exportFileToResponses(List<T> excelDataList,
                                                  ResponseExcel responseExcel,
                                                  Class<T> headClass,
                                                  HttpServletResponse response){
        exportFileToResponses(excelDataList,headClass,
                responseExcel.sheetName(),responseExcel.excelName(),responseExcel.excelSuffix(),response);

    }
    /**
     * 导出excel
     * @param excelDataList 数据泪飙
     * @param excelDataClass 数据类型
     * @param sheetName sheet名称
     * @param excelName excel文件名称
     * @param excelContentType 文件后缀
     * @param response 响应
     * @param <T> 泛型
     */
    public static <T> void  exportFileToResponses(List<T> excelDataList,
          Class<T> excelDataClass,
          String sheetName,
          String excelName,
          ExcelContentType excelContentType, HttpServletResponse response){
        WritableWorkbook workbook = null;
        OutputStream os = null;
        try {
            excelName = URLEncoder.encode(excelName,"UTF-8");
            os = response.getOutputStream();
            workbook = Workbook.createWorkbook(os);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+excelName+ "-" + sdf.format(new Date())+ "." + excelContentType.getSuffix());
            response.setContentType(excelContentType.getContentType());

            //标题
            WritableSheet wSheet = workbook.createSheet(sheetName,0);
            //头格式
            WritableCellFormat writableCellFormatTitle = getWritableCellFormatTitle(Colour.BLACK);
            WritableCellFormat writableCellFormatBody = getWritableCellFormatBody();
            //写入头信息==================start============================
            List<Field> annotatedFields = new ArrayList<>();
            ReflectionUtils.doWithFields(excelDataClass,annotatedFields::add,excelFieldFilter);
            annotatedFields.sort(Comparator.comparingInt(f -> f.getAnnotation(ExcelProperty.class).index()));
            int rowIndex = 0;
            for (int colIndex = 0; colIndex < annotatedFields.size(); colIndex++) {
                Field field = annotatedFields.get(colIndex);
                ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                if (annotation.headFontColor()!=FontColorEnum.BLACK){
                    wSheet.addCell(new Label(colIndex,rowIndex,annotation.value(),getWritableCellFormatTitle(colorMap.get(annotation.headFontColor()))));
                }else{
                    wSheet.addCell(new Label(colIndex,rowIndex,annotation.value(),writableCellFormatTitle));
                }
                // 设置宽度，如果宽度为0 代表隐藏
                wSheet.setColumnView(colIndex,annotation.columnWidth());
            }
            //写入头信息==================end============================
            rowIndex++;
            for (T excelData : excelDataList) {
                for (int colIndex = 0; colIndex < annotatedFields.size(); colIndex++) {
                    Field annotatedField = annotatedFields.get(colIndex);
                    ReflectionUtils.makeAccessible(annotatedField);
                    Object cellData = annotatedField.get(excelData);
                    cellData = cellData == null ? "":cellData.toString();
                    wSheet.addCell(new Label(colIndex,rowIndex,cellData.toString(),writableCellFormatBody));
                }
                rowIndex++;
            }
            // 列方向数据合并行数据
            for (int colIndex = 0; colIndex < annotatedFields.size(); colIndex++) {
                ExcelProperty annotation = annotatedFields.get(colIndex).getAnnotation(ExcelProperty.class);
                if (annotation.mergeRowData()){
                    merge(wSheet,colIndex,annotation.mergeDependencyColIndex());
                }
            }
            workbook.write();
        } catch (IOException | WriteException | IllegalAccessException e) {
            log.error("导出excel失败： {}", e);
            throw new ExcelExportException("Excel 导出失败", e);
        } finally {
            if (workbook != null){
                try {
                    workbook.close();
                } catch (IOException | WriteException e) {
                    log.error("关闭 Workbook 时发生错误, {}", e);
                }
            }
        }
    }

    public static <T> List<T> importExcel(InputStream inputStream, Class<T> headClass,int headNumber){
        Map<Integer,Field> annotationFieldMap = new HashMap<>();
        ReflectionUtils.doWithFields(headClass,field -> {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            field.setAccessible(true);
            annotationFieldMap.put(annotation.index(),field);
        },excelFieldFilter);
        List<T> excelData = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(0);
            int rows = sheet.getRows();
            int columns = sheet.getColumns();
            //获取合并单元格的信息
            Range[] mergedCells = sheet.getMergedCells();
            // 处理合并单元格
            Map<String,String> cellContentsMap =  new HashMap<>();
            for (Range range : mergedCells) {
                Cell topLeft = range.getTopLeft();
                Cell bottomRight = range.getBottomRight();
                int firstRow = topLeft.getRow();
                int lastRow = bottomRight.getRow();
                int firstCol = topLeft.getColumn();
                int lastCol = bottomRight.getColumn();
                String topLeftCellContents =  topLeft.getContents().trim();
                for (int row = firstRow; row <= lastRow; row++) {
                    for (int col = firstCol; col <= lastCol; col++) {
                        Cell cell = sheet.getCell(col, row);

                        if (!StringUtils.hasText(cell.getContents())){
                            cellContentsMap.put(row+rowColSeparator+col,topLeftCellContents);
                        }
                    }
                }
            }
            // 跳过表头行
            for (int row = headNumber; row < rows; row++) {
                // 数据项
                T data = headClass.newInstance();
                for (int col = 0; col < columns; col++) {
                    Cell cell = sheet.getCell(col, row);
                    String contents = cell.getContents();
                    Field field = annotationFieldMap.get(col);
                    if (!StringUtils.hasText(contents)){
                        // 尝试从map中获取数据
                        contents = cellContentsMap.get(row+rowColSeparator+col);
                    }
                    // 类型转换
                    Object value = convertStringToFieldType(contents,field.getType());
                    field.set(data,value);
                }
                excelData.add(data);
            }

        } catch (BiffException | IOException | InstantiationException |IllegalAccessException e) {
            log.error("导入excel失败: {}", e);
            throw new ExcelImportException("导入excel失败",e);
        } finally {
            if (workbook != null){
                workbook.close();
            }
        }
        return excelData;
    }

    private static Object convertStringToFieldType(String str, Class<?> type) {
        if (str == null || str.isEmpty()){
            return null;
        }
        if (type == String.class){
            return str;
        }
        if (type == Long.class || type == long.class){
            return Long.valueOf(str);
        }
        if (type == Integer.class || type == int.class){
            return Integer.valueOf(str);
        }
        if (type == Double.class || type == double.class){
            return Double.valueOf(str);
        }
        if (type == Float.class || type == float.class){
            return Float.valueOf(str);
        }
        if (type == Boolean.class || type == boolean.class){
            return Boolean.valueOf(str);
        }
        if (type == BigDecimal.class){
            return new BigDecimal(str);
        }
        return null;

    }


    private static WritableCellFormat getWritableCellFormatTitle(Colour colour) throws WriteException {
        WritableFont font = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,colour);
        WritableCellFormat titleCell = new WritableCellFormat(font);
        titleCell.setBorder(Border.ALL, BorderLineStyle.THIN);
        titleCell.setWrap(true);
        titleCell.setAlignment(Alignment.CENTRE);
        titleCell.setVerticalAlignment(VerticalAlignment.CENTRE);
        return titleCell;
    }
    private static WritableCellFormat getWritableCellFormatBody() throws WriteException {
        WritableFont font = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
        WritableCellFormat bodyCell = new WritableCellFormat(font);
        bodyCell.setBorder(Border.ALL, BorderLineStyle.THIN);
        bodyCell.setWrap(true);
        bodyCell.setAlignment(Alignment.CENTRE);
        bodyCell.setVerticalAlignment(VerticalAlignment.CENTRE);
        return bodyCell;
    }
    private static void merge(WritableSheet sheet,int colIndex,int[] dependencyColIndex)throws WriteException{
        boolean hasDependency = dependencyColIndex.length >0;
        int rows =  sheet.getRows();
        int startRow = 1;
        String currentContent = sheet.getCell(colIndex,startRow).getContents();
        if (hasDependency) {
            currentContent = getFullContentFromDependency(colIndex, startRow, sheet, dependencyColIndex);
        }
        for (int row = startRow + 1;row < rows;row++){
            Cell cell = sheet.getCell(colIndex,row);
            String contents = cell.getContents();
            if (hasDependency){
                contents = getFullContentFromDependency(colIndex,row,sheet,dependencyColIndex);
            }
            if (!contents.equals(currentContent)){
                //合并从startRow到rOW-1的单元格
                sheet.mergeCells(colIndex,startRow,colIndex,row -1);
                System.out.println("Merged cells:Column " + colIndex +"Rows " + startRow + " to " + (row - 1));
                startRow = row;;
                currentContent = contents;
            }
        }
        //合并最后一组单元格
        if (startRow < rows){
            sheet.mergeCells(colIndex,startRow,colIndex,rows -1);
            System.out.println("Merged cells:Column " + colIndex +"Rows " + startRow + " to " + (rows - 1));
        }
    }

    private static String getFullContentFromDependency(int colIndex, int startRow, WritableSheet sheet, int[] dependencyColIndex) {
        StringBuffer sb = new StringBuffer();
        for (int dependencyCol : dependencyColIndex) {
            sb.append(sheet.getCell(dependencyCol,startRow).getContents());
        }
        sb.append(sheet.getCell(colIndex,startRow).getContents());
        return sb.toString();
    }
}
